package hesso.smartdraw.ch.db;

import java.util.ArrayList;
import hesso.smartdraw.ch.model.Building;
import hesso.smartdraw.ch.model.Consumation;
import hesso.smartdraw.ch.model.Device;
import hesso.smartdraw.ch.model.Input;
import hesso.smartdraw.ch.model.Installed;
import hesso.smartdraw.ch.model.Layer;
import hesso.smartdraw.ch.model.Level;
import hesso.smartdraw.ch.model.Output;
import hesso.smartdraw.ch.model.Room;
import hesso.smartdraw.ch.model.Unit;
import hesso.smartdraw.ch.model.User;
import hesso.smartdraw.ch.model.Wall;
import hesso.smartdraw.ch.soap.SOAPHelper;
import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {

	private static final String DB_NAME = "FIREFLY";
	private static final int DB_VERSION = 150;
	
	private static DBHelper instance;
	SQLiteDatabase dbConn;
	
	Building building;
	Level level;
	Room room;
	Wall wall;
	Installed installed;
	Device device;
	Layer layer;
	Input input;
	Output output;
	Consumation consumation;
	Unit unit;
	int nextId = 0;
	Cursor a;
	Cursor b;
	Cursor c;
	Cursor d;
	Cursor e;
	Cursor f;
	Cursor g;
	Activity context;
	SOAPHelper sHelper;
	
	private DBHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
		this.context = (Activity) context;
	}
	
	public void initDBConn() {
		dbConn = instance.getWritableDatabase();
	}
	
	public static DBHelper getInstance(Context context){
		if(instance == null){
			instance = new DBHelper(context);
			return instance;
		}
		else{
			return instance;
		}
	}

	public SQLiteDatabase getDbConn() {
		return dbConn;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE IF NOT EXISTS tbl_Buildings"
				+ " ( buildingId INTEGER PRIMARY KEY AUTOINCREMENT, "
                + "   buildingName VARCHAR(45), "
                + "   buildingVersion INTEGER, "
                + "   tbl_User_userId INTEGER, "
                + "   FOREIGN KEY(tbl_User_userId) REFERENCES tbl_User(userId))"
                + ";");

		db.execSQL("CREATE TABLE IF NOT EXISTS tbl_Levels"
				+ " ( levelId INTEGER PRIMARY KEY AUTOINCREMENT, "
                + "   levelName VARCHAR(45), "
                + "   levelLevel INTEGER, "
                + "   tbl_Buildings_buildingId INTEGER, "
                + "   FOREIGN KEY(tbl_Buildings_buildingId) REFERENCES tbl_Buildings(buildingId) ON DELETE CASCADE)"
                + ";");

		db.execSQL("CREATE TABLE IF NOT EXISTS tbl_Rooms"
				+ " ( roomId INTEGER PRIMARY KEY AUTOINCREMENT, "
                + "   roomName VARCHAR(45), "
                + "   tbl_Levels_levelId INTEGER, "
                + "   FOREIGN KEY(tbl_Levels_levelId) REFERENCES tbl_Levels(levelId) ON DELETE CASCADE)"
                + ";");

		db.execSQL("CREATE TABLE IF NOT EXISTS tbl_Walls"
				+ " ( wallId INTEGER PRIMARY KEY AUTOINCREMENT, "
                + "   wallName VARCHAR(45), "
                + "   wallX1 INTEGER, "
                + "   wallX2 INTEGER, "
                + "   wallY1 INTEGER, "
                + "   wallY2 INTEGER, "
                + "   wallHeight INTEGER, "
                + "   wallDepth INTEGER, "
                + "   wallType INTEGER, "
                + "   tbl_Rooms_roomId INTEGER, "
                + "   FOREIGN KEY(tbl_Rooms_roomId) REFERENCES tbl_Rooms(roomId) ON DELETE CASCADE)"
                + ";");

		db.execSQL("CREATE TABLE IF NOT EXISTS tbl_Installed"
				+ " ( installedId INTEGER PRIMARY KEY AUTOINCREMENT, "
                + "   installedName VARCHAR(45), "
                + "   installedX INTEGER, "
                + "   installedY INTEGER, "
                + "   installedHeight INTEGER, "
                + "   installedWidth INTEGER, "
                + "   tbl_Devices_deviceId INTEGER, "
                + "   tbl_Walls_wallId INTEGER, "
                + "   FOREIGN KEY(tbl_Devices_deviceId) REFERENCES tbl_Devices(deviceId), "
                + "   FOREIGN KEY(tbl_Walls_wallId) REFERENCES tbl_Walls(wallId) ON DELETE CASCADE)"
                + ";");

		db.execSQL("CREATE TABLE IF NOT EXISTS tbl_Devices"
				+ " ( deviceId INTEGER PRIMARY KEY AUTOINCREMENT, "
                + "   deviceName VARCHAR(45), "
                + "   tbl_Layers_layerId INTEGER, "
                + "   FOREIGN KEY(tbl_Layers_layerId) REFERENCES tbl_Layers(layerId))"
                + ";");

		db.execSQL("CREATE TABLE IF NOT EXISTS tbl_Layers"
				+ " ( layerId INTEGER PRIMARY KEY AUTOINCREMENT, "
                + "   layerName VARCHAR(45))"
                + ";");

		db.execSQL("CREATE TABLE IF NOT EXISTS tbl_Consumations"
				+ " ( consumationId INTEGER PRIMARY KEY AUTOINCREMENT, "
                + "   consumationValue INTEGER, "
				+ "   tbl_Installed_installedId INTEGER, "
                + "   tbl_Units_unitId INTEGER, "
                + "   FOREIGN KEY(tbl_Installed_installedId) REFERENCES tbl_Installed(installedId) ON DELETE CASCADE, "
                + "   FOREIGN KEY(tbl_Units_unitId) REFERENCES tbl_Units(unitId))"
                + ";");

		db.execSQL("CREATE TABLE IF NOT EXISTS tbl_Units"
				+ " ( unitId INTEGER PRIMARY KEY AUTOINCREMENT, "
                + "   unitName VARCHAR(45), "
                + "   unitUnit VARCHAR(45))"
                + ";");

		db.execSQL("CREATE TABLE IF NOT EXISTS tbl_Inputs"
				+ " ( inputId INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ "   tbl_Devices_deviceId INTEGER, "
				+ "   tbl_Units_unitId INTEGER, "
                + "   FOREIGN KEY(tbl_Devices_deviceId) REFERENCES tbl_Devices(deviceId), "
                + "   FOREIGN KEY(tbl_Units_unitId) REFERENCES tbl_Units(unitId))"
                + ";");

		db.execSQL("CREATE TABLE IF NOT EXISTS tbl_Outputs"
				+ " ( outputId INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ "   tbl_Devices_deviceId INTEGER, "
                + "   tbl_Units_unitId INTEGER, "
                + "   FOREIGN KEY(tbl_Devices_deviceId) REFERENCES tbl_Devices(deviceId), "
                + "   FOREIGN KEY(tbl_Units_unitId) REFERENCES tbl_Units(unitId))"
                + ";");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS tbl_Roles");
		db.execSQL("DROP TABLE IF EXISTS tbl_User");
		db.execSQL("DROP TABLE IF EXISTS tbl_Buildings");
		db.execSQL("DROP TABLE IF EXISTS tbl_Levels");
		db.execSQL("DROP TABLE IF EXISTS tbl_Rooms");
		db.execSQL("DROP TABLE IF EXISTS tbl_Walls");
		db.execSQL("DROP TABLE IF EXISTS tbl_CeilFloor");
		db.execSQL("DROP TABLE IF EXISTS tbl_Installed_has_tbl_Walls");
		db.execSQL("DROP TABLE IF EXISTS tbl_Installed_has_tbl_CeilFloor");
		db.execSQL("DROP TABLE IF EXISTS tbl_Installed");
		db.execSQL("DROP TABLE IF EXISTS tbl_Devices");
		db.execSQL("DROP TABLE IF EXISTS tbl_Layers");
		db.execSQL("DROP TABLE IF EXISTS tbl_Consumations");
		db.execSQL("DROP TABLE IF EXISTS tbl_Units");
		db.execSQL("DROP TABLE IF EXISTS tbl_Inputs");
		db.execSQL("DROP TABLE IF EXISTS tbl_Outputs");
		onCreate(db);
	}
	
	public ArrayList<Building> getBuildingsByUserId(int userId){
		ArrayList<Building> buildings = new ArrayList<Building>();
		
		a = dbConn.rawQuery("SELECT" +
				" buildingId," +
				" buildingName" +
				" FROM tbl_Buildings" +
				" WHERE tbl_User_userId = " + userId, null);
		
		context.startManagingCursor(a);
        a.moveToFirst();
        
        for(int i=0; i<a.getCount(); i++){
    		buildings.add(new Building(a.getInt(0), a.getString(1)));
    		
    		a.moveToNext();
        }
        
        return buildings;
	}
	
	public ArrayList<Device> getDevices(){
		ArrayList<Device> devices = new ArrayList<Device>();
		
		//get device(type) from db
		e = dbConn.rawQuery("SELECT" +
				" tbl_Devices.deviceId," +
				" tbl_Devices.deviceName," +
				" tbl_Devices.tbl_Layers_layerId" +
				" FROM tbl_Devices", null);
		
		context.startManagingCursor(e);
        e.moveToFirst();
        
        for(int m=0; m<e.getCount(); m++){
        	//set device(type)
        	device = new Device();
    		device.setId(e.getInt(0));
    		device.setName(e.getString(1));
    		//add device(type) to installed device
//    		installed.setDevice(device);
    		    		
    		//get layer from db
    		f = dbConn.rawQuery("SELECT" +
    				" tbl_Layers.layerId," +
    				" tbl_Layers.layerName" +
    				" FROM tbl_Layers" +
    				" WHERE tbl_Layers.layerId = " + e.getInt(2) +
    				" LIMIT 1", null);
    		
    		context.startManagingCursor(f);
            f.moveToFirst();
            
            for(int n=0; n<f.getCount(); n++){
            	//set layer
            	layer = new Layer();
        		layer.setId(f.getInt(0));
        		layer.setName(f.getString(1));
        		//add layer to device(type)
        		device.setLayer(layer);
            	
            	f.moveToNext();
            }
    		
    		//get inputs from db
    		f = dbConn.rawQuery("SELECT" +
    				" tbl_Inputs.tbl_Units_unitId" +
    				" FROM tbl_Inputs" +
    				" WHERE tbl_Devices_deviceId = " + device.getId(), null);
    		
    		context.startManagingCursor(f);
            f.moveToFirst();
            
            for(int n=0; n<f.getCount(); n++){
            	//get unit for input from db
        		g = dbConn.rawQuery("SELECT" +
        				" tbl_Units.unitId," +
        				" tbl_Units.unitName," +
        				" tbl_Units.unitUnit" +
        				" FROM tbl_Units" +
        				" WHERE tbl_Units.unitId = " + f.getInt(0) +
        				" LIMIT 1", null);
        		
        		context.startManagingCursor(g);
                g.moveToFirst();
                
                for(int o=0; o<g.getCount(); o++){
                	//set input
                	input = new Input();
            		input.setUnit(new Unit(g.getInt(0), g.getString(1), g.getString(2)));
            		//add input to device(type)
            		device.addInput(input);
                	
                	g.moveToNext();
                }
            	
            	f.moveToNext();
            }
    		
    		//get outputs from db
    		f = dbConn.rawQuery("SELECT" +
    				" tbl_Outputs.tbl_Units_unitId" +
    				" FROM tbl_Outputs" +
    				" WHERE tbl_Devices_deviceId = " + device.getId(), null);
    		
    		context.startManagingCursor(f);
            f.moveToFirst();
            
            for(int n=0; n<f.getCount(); n++){
            	//get unit for output from db
        		g = dbConn.rawQuery("SELECT" +
        				" tbl_Units.unitId," +
        				" tbl_Units.unitName," +
        				" tbl_Units.unitUnit" +
        				" FROM tbl_Units" +
        				" WHERE tbl_Units.unitId = " + f.getInt(0) +
        				" LIMIT 1", null);
        		
        		context.startManagingCursor(g);
                g.moveToFirst();
                
                for(int o=0; o<g.getCount(); o++){
            		//set output
                	output = new Output();
            		output.setUnit(new Unit(g.getInt(0), g.getString(1), g.getString(2)));
            		//add input to device(type)
            		device.addOutput(output);
                	
                	g.moveToNext();
                }
            	
            	f.moveToNext();
            }
        	
        	e.moveToNext();
        	
        	devices.add(device);
        }
        
        return devices;
	}
	
	public Building getBuildingByBuildingId(int buildingId){		
		//get building from db
		a = dbConn.rawQuery("SELECT" +
				" tbl_Buildings.buildingId," +
				" tbl_Buildings.buildingName" +
				" FROM tbl_Buildings" +
				" WHERE tbl_Buildings.buildingId = " + buildingId +
				" LIMIT 1", null);
		
		context.startManagingCursor(a);
        a.moveToFirst();
        
		if(a.getCount() != 0){
			//set building
			building = new Building();
			building.setId(a.getInt(0));
			building.setName(a.getString(1));
        }
		else{
			return null;
		}
		
		//get level(s) from db
		a = dbConn.rawQuery("SELECT" +
				" tbl_Levels.levelId," +
				" tbl_Levels.levelName," +
				" tbl_Levels.levelLevel" +
				" FROM tbl_Levels" +
				" INNER JOIN tbl_Buildings" +
				" ON tbl_Levels.tbl_Buildings_buildingId = tbl_Buildings.buildingId" +
				" WHERE tbl_Buildings.buildingId = " + building.getId(), null);
		
		context.startManagingCursor(a);
        a.moveToFirst();

        for(int i=0; i<a.getCount(); i++){
        	//set level
        	level = new Level();
    		level.setId(a.getInt(0));
    		level.setName(a.getString(1));
    		level.setLevel(a.getInt(2));
    		//add level to building
    		building.addLevel(level);
    		
    		//get room(s) from db
    		b = dbConn.rawQuery("SELECT" +
    				" tbl_Rooms.roomId," +
    				" tbl_Rooms.roomName" +
    				" FROM tbl_Rooms" +
    				" INNER JOIN tbl_Levels" +
    				" ON tbl_Rooms.tbl_Levels_levelId = tbl_Levels.levelId" +
    				" WHERE tbl_Levels.levelId = " + level.getId(), null);
    		
    		context.startManagingCursor(b);
            b.moveToFirst();

            for(int j=0; j<b.getCount(); j++){
            	//set room
            	room = new Room();
        		room.setId(b.getInt(0));
        		room.setName(b.getString(1));
        		//add room to level
        		level.addRoom(room);
        		
        		//get wall(s) from db
        		c = dbConn.rawQuery("SELECT" +
        				" tbl_Walls.wallId," +
        				" tbl_Walls.wallName," +
        				" tbl_Walls.wallX1," +
        				" tbl_Walls.wallX2," +
        				" tbl_Walls.wallY1," +
        				" tbl_Walls.wallY2," +
        				" tbl_Walls.wallHeight," +
        				" tbl_Walls.wallDepth," +
        				" tbl_Walls.wallType" +
        				" FROM tbl_Walls" +
        				" INNER JOIN tbl_Rooms" +
        				" ON tbl_Walls.tbl_Rooms_roomId = tbl_Rooms.roomId" +
        				" WHERE tbl_Rooms.roomId = " + room.getId(), null);
        		
        		context.startManagingCursor(c);
                c.moveToFirst();

                for(int k=0; k<c.getCount(); k++){
                	//set wall
                	wall = new Wall();
            		wall.setId(c.getInt(0));
            		wall.setName(c.getString(1));
            		wall.setX1(c.getInt(2));
            		wall.setX2(c.getInt(3));
            		wall.setY1(c.getInt(4));
            		wall.setY2(c.getInt(5));
            		wall.setHeight(c.getInt(6));
            		wall.setDepth(c.getInt(7));
            		wall.setWallType(c.getInt(8));
            		//add wall to room
            		room.addWall(wall);
            		
            		//get installed device(s) from db
            		d = dbConn.rawQuery("SELECT" +
            				" i.installedId," +
            				" i.installedName," +
            				" i.installedX," +
            				" i.installedY," +
            				" i.installedHeight," +
            				" i.installedWidth," +
            				" i.tbl_Devices_deviceId," +
            				" i.tbl_Walls_wallId" +
            				" FROM tbl_Installed as i" +
            				" INNER JOIN tbl_Walls as w" +
            				" ON i.tbl_Walls_wallId = w.wallId" +
            				" WHERE w.wallId = " + wall.getId(), null);
            		
            		context.startManagingCursor(d);
                    d.moveToFirst();
                    
                    for(int l=0; l<d.getCount(); l++){
                    	//set installed device
                    	installed = new Installed();
                		installed.setId(d.getInt(0));
                		installed.setName(d.getString(1));
                		installed.setX(d.getInt(2));
                		installed.setY(d.getInt(3));
                		installed.setHeight(d.getInt(4));
                		installed.setWidth(d.getInt(5));
                		//add installed device to wall
                		wall.addInstalled(installed);
                		
                		getInfoForInstalledDevice();
                    	
                    	d.moveToNext();
                    }
                	
                	c.moveToNext();
                }
            	
            	b.moveToNext();
            }
            
            a.moveToNext();
        }
        
		return building;
	}
	
	public Room getRoomByRoomId(int roomId){
		//get room from db
		c = dbConn.rawQuery("SELECT" +
				" tbl_Rooms.roomId," +
				" tbl_Rooms.roomName" +
				" FROM tbl_Rooms" +
				" WHERE tbl_Rooms.roomId = " + roomId +
				" LIMIT 1", null);
		
		context.startManagingCursor(c);
        c.moveToFirst();
        
		if(c.getCount() != 0){
			//set room
			room = new Room();
			room.setId(c.getInt(0));
			room.setName(c.getString(1));
        }
		else{
			return null;
		}
		
		//get wall(s) from db
		c = dbConn.rawQuery("SELECT" +
				" tbl_Walls.wallId," +
				" tbl_Walls.wallName," +
				" tbl_Walls.wallX1," +
				" tbl_Walls.wallX2," +
				" tbl_Walls.wallY1," +
				" tbl_Walls.wallY2," +
				" tbl_Walls.wallHeight," +
				" tbl_Walls.wallDepth," +
				" tbl_Walls.wallType" +
				" FROM tbl_Walls" +
				" INNER JOIN tbl_Rooms" +
				" ON tbl_Walls.tbl_Rooms_roomId = tbl_Rooms.roomId" +
				" WHERE tbl_Rooms.roomId = " + room.getId(), null);
		
		context.startManagingCursor(c);
        c.moveToFirst();

        for(int k=0; k<c.getCount(); k++){
        	//set wall
        	wall = new Wall();
    		wall.setId(c.getInt(0));
    		wall.setName(c.getString(1));
    		wall.setX1(c.getInt(2));
    		wall.setX2(c.getInt(3));
    		wall.setY1(c.getInt(4));
    		wall.setY2(c.getInt(5));
    		wall.setHeight(c.getInt(6));
    		wall.setDepth(c.getInt(7));
    		wall.setWallType(c.getInt(8));
    		//add wall to room
    		room.addWall(wall);
    		
    		//get installed device(s) from db
    		d = dbConn.rawQuery("SELECT" +
    				" i.installedId," +
    				" i.installedName," +
    				" i.installedX," +
    				" i.installedY," +
    				" i.installedHeight," +
    				" i.installedWidth," +
    				" i.tbl_Devices_deviceId," +
    				" i.tbl_Walls_wallId" +
    				" FROM tbl_Installed as i" +
    				" INNER JOIN tbl_Walls as w" +
    				" ON i.tbl_Walls_wallId = w.wallId" +
    				" WHERE w.wallId = " + wall.getId(), null);
    		
    		context.startManagingCursor(d);
            d.moveToFirst();
            
            for(int l=0; l<d.getCount(); l++){
            	//set installed device
            	installed = new Installed();
        		installed.setId(d.getInt(0));
        		installed.setName(d.getString(1));
        		installed.setX(d.getInt(2));
        		installed.setY(d.getInt(3));
        		installed.setHeight(d.getInt(4));
        		installed.setWidth(d.getInt(5));
        		//add installed device to wall
        		wall.addInstalled(installed);
        		
        		getInfoForInstalledDevice();
            	
            	d.moveToNext();
            }
        	
        	c.moveToNext();
        }
        
        return room;
	}
	
	public Installed getInstalledByInstalledId(int installedId){
		//get installed device(s) from db
		d = dbConn.rawQuery("SELECT" +
				" installedId," +
				" installedName," +
				" installedX," +
				" installedY," +
				" installedHeight," +
				" installedWidth," +
				" tbl_Devices_deviceId" +
				" FROM tbl_Installed" +
				" WHERE installedId = " + installedId + 
				" LIMIT 1", null);
		
		context.startManagingCursor(d);
        d.moveToFirst();

		if(d.getCount() != 0){
			//set installed device
        	installed = new Installed();
    		installed.setId(d.getInt(0));
    		installed.setName(d.getString(1));
    		installed.setX(d.getInt(2));
    		installed.setY(d.getInt(3));
    		installed.setHeight(d.getInt(4));
    		installed.setWidth(d.getInt(5));
    		
    		getInfoForInstalledDevice();
        	
        	d.moveToNext();
        }
		else{
			return null;
		}
		
		return installed;
	}
	
	public void getInfoForInstalledDevice(){
		//get device(type) from db
		e = dbConn.rawQuery("SELECT" +
				" tbl_Devices.deviceId," +
				" tbl_Devices.deviceName," +
				" tbl_Devices.tbl_Layers_layerId" +
				" FROM tbl_Devices" +
				" WHERE tbl_Devices.deviceId = " + d.getInt(6) +
				" LIMIT 1", null);
		
		context.startManagingCursor(e);
        e.moveToFirst();
        
        if(e.getCount() != 0){
        	//set device(type)
        	device = new Device();
    		device.setId(e.getInt(0));
    		device.setName(e.getString(1));
    		//add device(type) to installed device
    		installed.setDevice(device);
    		    		
    		//get layer from db
    		f = dbConn.rawQuery("SELECT" +
    				" tbl_Layers.layerId," +
    				" tbl_Layers.layerName" +
    				" FROM tbl_Layers" +
    				" WHERE tbl_Layers.layerId = " + e.getInt(2) +
    				" LIMIT 1", null);
    		
    		context.startManagingCursor(f);
            f.moveToFirst();
            
            for(int n=0; n<f.getCount(); n++){
            	//set layer
            	layer = new Layer();
        		layer.setId(f.getInt(0));
        		layer.setName(f.getString(1));
        		//add layer to device(type)
        		device.setLayer(layer);
            	
            	f.moveToNext();
            }
    		
    		//get inputs from db
    		f = dbConn.rawQuery("SELECT" +
    				" tbl_Inputs.tbl_Units_unitId" +
    				" FROM tbl_Inputs" +
    				" WHERE tbl_Devices_deviceId = " + device.getId(), null);
    		
    		context.startManagingCursor(f);
            f.moveToFirst();
            
            for(int n=0; n<f.getCount(); n++){
            	//get unit for input from db
        		g = dbConn.rawQuery("SELECT" +
        				" tbl_Units.unitId," +
        				" tbl_Units.unitName," +
        				" tbl_Units.unitUnit" +
        				" FROM tbl_Units" +
        				" WHERE tbl_Units.unitId = " + f.getInt(0) +
        				" LIMIT 1", null);
        		
        		context.startManagingCursor(g);
                g.moveToFirst();
                
                for(int o=0; o<g.getCount(); o++){
                	//set input
                	input = new Input();
            		input.setUnit(new Unit(g.getInt(0), g.getString(1), g.getString(2)));
            		//add input to device(type)
            		device.addInput(input);
                	
                	g.moveToNext();
                }
            	
            	f.moveToNext();
            }
    		
    		//get outputs from db
    		f = dbConn.rawQuery("SELECT" +
    				" tbl_Outputs.tbl_Units_unitId" +
    				" FROM tbl_Outputs" +
    				" WHERE tbl_Devices_deviceId = " + device.getId(), null);
    		
    		context.startManagingCursor(f);
            f.moveToFirst();
            
            for(int n=0; n<f.getCount(); n++){
            	//get unit for output from db
        		g = dbConn.rawQuery("SELECT" +
        				" tbl_Units.unitId," +
        				" tbl_Units.unitName," +
        				" tbl_Units.unitUnit" +
        				" FROM tbl_Units" +
        				" WHERE tbl_Units.unitId = " + f.getInt(0) +
        				" LIMIT 1", null);
        		
        		context.startManagingCursor(g);
                g.moveToFirst();
                
                for(int o=0; o<g.getCount(); o++){
            		//set output
                	output = new Output();
            		output.setUnit(new Unit(g.getInt(0), g.getString(1), g.getString(2)));
            		//add input to device(type)
            		device.addOutput(output);
                	
                	g.moveToNext();
                }
            	
            	f.moveToNext();
            }
        	
        	e.moveToNext();
        }
		
		//get consumations for installed device from db
		e = dbConn.rawQuery("SELECT" +
				" tbl_Consumations.consumationId," +
				" tbl_Consumations.consumationValue," +
				" tbl_Consumations.tbl_Units_unitId" +
				" FROM tbl_Consumations" +
				" WHERE tbl_Consumations.tbl_Installed_installedId = " + installed.getId(), null);
		
		context.startManagingCursor(e);
        e.moveToFirst();
        
        for(int m=0; m<e.getCount(); m++){
        	//set consumation for installed device
        	consumation = new Consumation();
        	consumation.setId(e.getInt(0));
    		consumation.setValue(e.getInt(1));
    		//add consumation to installed device
    		installed.addConsumation(consumation);
    		
    		//get unit for consumation from db
    		f = dbConn.rawQuery("SELECT" +
    				" tbl_Units.unitId," +
    				" tbl_Units.unitName," +
    				" tbl_Units.unitUnit" +
    				" FROM tbl_Units" +
    				" WHERE tbl_Units.unitId = " + e.getInt(2) +
    				" LIMIT 1", null);
    		
    		context.startManagingCursor(f);
            f.moveToFirst();
            
            for(int n=0; n<f.getCount(); n++){
        		//set unit
            	unit = new Unit();
        		unit.setId(f.getInt(0));
        		unit.setName(f.getString(1));
        		unit.setUnit(f.getString(2));
        		//add unit to consumation
        		consumation.setUnit(unit);
            	
            	f.moveToNext();
            }
        	
        	e.moveToNext();
        }
	}
		
	@SuppressWarnings("static-access")
	public Building saveBuilding(Building building, User user){
		//insert	
		if(building.getId() == 0){
			//insert building
			nextId = sHelper.getNewBuildingId();
			dbConn.execSQL("INSERT INTO tbl_Buildings" +
					" (buildingId, buildingName, buildingVersion, tbl_User_userId) VALUES" +
					" ('" + nextId + "', '" + building.getName() + "', '" + 0 + "', '" + user.getId() + "')");

	        building.setId(nextId);
	        
			for(int i=0; i<building.getLevelArray().size(); i++){
				Level level = building.getLevelArray().get(i);
				//insert level
				nextId = sHelper.getNewLevelId();
				dbConn.execSQL("INSERT INTO tbl_Levels" +
						" (levelId, levelName, levelLevel, tbl_Buildings_buildingId) VALUES" +
						" ('" + nextId + "', '" + level.getName() + "', '" + level.getLevel() + "', '" + building.getId() + "')");

	            level.setId(nextId);
	            
				for(int j=0; j<level.getRoomArray().size(); j++){
					Room room = level.getRoomArray().get(j);
					//insert room
					nextId = sHelper.getNewRoomId();
	    			dbConn.execSQL("INSERT INTO tbl_Rooms" +
	    					" (roomId, roomName, tbl_Levels_levelId) VALUES" +
	    					" ('" + nextId + "', '" + room.getName() + "', '" + level.getId() + "')");

	                room.setId(nextId);
	                
					for(int k=0; k<room.getWallArray().size(); k++){
						Wall wall = room.getWallArray().get(k);
						//insert wall
						nextId = sHelper.getNewWallId();
		    			dbConn.execSQL("INSERT INTO tbl_Walls" +
		    					" (wallId, wallName, wallX1, wallX2, wallY1, wallY2, wallHeight, wallDepth, wallType, tbl_Rooms_roomId) VALUES" +
		    					" ('" + nextId + "', '" + wall.getName() + "', '" + wall.getX1() + "', '" + wall.getX2() + "', '" + wall.getY1() + "', '" + wall.getY2() + "', '" + wall.getHeight() + "', '" + wall.getDepth() + "', '" + wall.getWallType() + "', '" + room.getId() + "')");

		                wall.setId(nextId);
		                
						for(int l=0; l<wall.getInstalledArray().size(); l++){
							Installed installed = wall.getInstalledArray().get(l);
							//insert installed device
							nextId = sHelper.getNewInstalledId();
			    			dbConn.execSQL("INSERT INTO tbl_Installed" +
			    					" (installedId, installedName, installedX, installedY, installedHeight, installedWidth, tbl_Devices_deviceId, tbl_Walls_wallId) VALUES" +
			    					" ('" + nextId + "', '" + installed.getName() + "', '" + installed.getX() + "', '" + installed.getY() + "', '" + installed.getHeight() + "', '" + installed.getWidth() + "', '" + installed.getDevice().getId() + "', '" + wall.getId() + "')");

			                installed.setId(nextId);
			                
							for(int m=0; m<installed.getConsumationArray().size(); m++){
								Consumation consumation = installed.getConsumationArray().get(m);
								//insert consumation for device
								nextId = sHelper.getNewConsumationId();
				    			dbConn.execSQL("INSERT INTO tbl_Consumations" +
				    					" (consumationId, tbl_Installed_installedId, tbl_Units_unitId, consumationValue) VALUES" +
				    					" ('" + nextId + "', '" + installed.getId() + "', '" + consumation.getUnit().getId() + "', '" + consumation.getValue() + "')");
				    			
				    			consumation.setId(nextId);
							}
						}
					}
				}
			}
		}
		
		//update
		else{
			//update building
			dbConn.execSQL("UPDATE tbl_Buildings" +
					" SET buildingName = '" + building.getName() + "'" +
					" WHERE buildingId = " + building.getId());
			
			for(int i=0; i<building.getLevelArray().size(); i++){
				level = building.getLevelArray().get(i);
				
		        if(level.getId() != 0){
		        	//update level
					dbConn.execSQL("UPDATE tbl_Levels" +
							" SET levelName = '" + level.getName() + "'," +
							" levelLevel = '" + level.getLevel() + "'," +
							" tbl_Buildings_buildingId = '" + building.getId() + "'" +
							" WHERE levelId = " + level.getId());
		        }
		        else{
		        	//insert level
					nextId = sHelper.getNewLevelId();
					dbConn.execSQL("INSERT INTO tbl_Levels" +
							" (levelId, levelName, levelLevel, tbl_Buildings_buildingId) VALUES" +
							" ('" + nextId + "', '" + level.getName() + "', '" + level.getLevel() + "', '" + building.getId() + "')");

		            level.setId(nextId);
		        }
		        
		        for(int j=0; j<level.getRoomArray().size(); j++){
		        	room = level.getRoomArray().get(j);
		        	
			        if(room.getId() != 0){
			        	//update room
						dbConn.execSQL("UPDATE tbl_Rooms" +
								" SET roomName = '" + room.getName() + "'," +
								" tbl_Levels_levelId = '" + level.getId() + "'" +
								" WHERE roomId = " + room.getId());
			        }
			        else{
			        	//insert room
						nextId = sHelper.getNewRoomId();
		    			dbConn.execSQL("INSERT INTO tbl_Rooms" +
		    					" (roomId, roomName, tbl_Levels_levelId) VALUES" +
		    					" ('" + nextId + "', '" + room.getName() + "', '" + level.getId() + "')");

		                room.setId(nextId);
			        }
			        
			        for(int k=0; k<room.getWallArray().size(); k++){
			        	wall = room.getWallArray().get(k);
			        	
				        if(wall.getId() != 0){
				        	//update wall
							dbConn.execSQL("UPDATE tbl_Walls" +
									" SET wallName = '" + wall.getName() + "'," +
									" wallX1 = '" + wall.getX1() + "'," +
									" wallX2 = '" + wall.getX2() + "'," +
									" wallY1 = '" + wall.getY1() + "'," +
									" wallY2 = '" + wall.getY2() + "'," +
									" wallHeight = '" + wall.getHeight() + "'," +
									" wallDepth = '" + wall.getDepth() + "'," +
									" wallType = '" + wall.getWallType() + "'," +
									" tbl_Rooms_roomId = '" + room.getId() + "'" +
									" WHERE wallId = " + wall.getId());
				        }
				        else{
				        	//insert wall
							nextId = sHelper.getNewWallId();
			    			dbConn.execSQL("INSERT INTO tbl_Walls" +
			    					" (wallId, wallName, wallX1, wallX2, wallY1, wallY2, wallHeight, wallDepth, wallType, tbl_Rooms_roomId) VALUES" +
			    					" ('" + nextId + "', '" + wall.getName() + "', '" + wall.getX1() + "', '" + wall.getX2() + "', '" + wall.getY1() + "', '" + wall.getY2() + "', '" + wall.getHeight() + "', '" + wall.getDepth() + "', '" + wall.getWallType() + "', '" + room.getId() + "')");

			                wall.setId(nextId);
				        }
				        
				        for(int l=0; l<wall.getInstalledArray().size(); l++){
				        	installed = wall.getInstalledArray().get(l);
				        	
					        if(installed.getId() != 0){
					        	//update installed device
								dbConn.execSQL("UPDATE tbl_Installed" +
										" SET installedName = '" + installed.getName() + "'," +
										" installedX = '" + installed.getX() + "'," +
										" installedY = '" + installed.getY() + "'," +
										" installedHeight = '" + installed.getHeight() + "'," +
										" installedWidth = '" + installed.getWidth() + "'," +
										" tbl_Devices_deviceId = '" + installed.getDevice().getId() + "'" +
										" WHERE installedId = " + installed.getId());
					        }
					        else{
					        	//insert installed device
								nextId = sHelper.getNewInstalledId();
				    			dbConn.execSQL("INSERT INTO tbl_Installed" +
				    					" (installedId, installedName, installedX, installedY, installedHeight, installedWidth, tbl_Devices_deviceId, tbl_Walls_wallId) VALUES" +
				    					" ('" + nextId + "', '" + installed.getName() + "', '" + installed.getX() + "', '" + installed.getY() + "', '" + installed.getHeight() + "', '" + installed.getWidth() + "', '" + installed.getDevice().getId() + "', '" + wall.getId() + "')");

				                installed.setId(nextId);				        	
					        }
					        
					        for(int m=0; m<installed.getConsumationArray().size(); m++){
					        	consumation = installed.getConsumationArray().get(m);

						        if(consumation.getId() != 0){
						        	//update consumation for device
									dbConn.execSQL("UPDATE tbl_Consumations" +
											" SET tbl_Installed_installedId = '" + installed.getId() + "'," +
											" tbl_Units_unitId = '" + consumation.getUnit().getId() + "'," +
											" consumationValue = '" + consumation.getValue() + "'" +
											" WHERE consumationId = " + consumation.getId());
						        }
						        else{
						        	//insert consumation for device
									nextId = sHelper.getNewConsumationId();
					    			dbConn.execSQL("INSERT INTO tbl_Consumations" +
					    					" (consumationId, tbl_Installed_installedId, tbl_Units_unitId, consumationValue) VALUES" +
					    					" ('" + nextId + "', '" + installed.getId() + "', '" + consumation.getUnit().getId() + "', '" + consumation.getValue() + "')");

					                consumation.setId(nextId);
						        }
					        }
				        }
			        }
		        }
			}
		}
		
		setVersion(building.getId());
		
		return building;
	}
	
	@SuppressWarnings("static-access")
	public Room saveRoom(Room room, int levelId){
		//insert
		if(room.getId() == 0){
			//insert room
			nextId = sHelper.getNewRoomId();
			dbConn.execSQL("INSERT INTO tbl_Rooms" +
					" (roomId, roomName, tbl_Levels_levelId) VALUES" +
					" ('" + nextId + "', '" + room.getName() + "', '" + levelId + "')");

            room.setId(nextId);
            
			for(int k=0; k<room.getWallArray().size(); k++){
				Wall wall = room.getWallArray().get(k);
				//insert wall
				nextId = sHelper.getNewWallId();
    			dbConn.execSQL("INSERT INTO tbl_Walls" +
    					" (wallId, wallName, wallX1, wallX2, wallY1, wallY2, wallHeight, wallDepth, wallType, tbl_Rooms_roomId) VALUES" +
    					" ('" + nextId + "', '" + wall.getName() + "', '" + wall.getX1() + "', '" + wall.getX2() + "', '" + wall.getY1() + "', '" + wall.getY2() + "', '" + wall.getHeight() + "', '" + wall.getDepth() + "', '" + wall.getWallType() + "', '" + room.getId() + "')");

                wall.setId(nextId);
                
				for(int l=0; l<wall.getInstalledArray().size(); l++){
					Installed installed = wall.getInstalledArray().get(l);
					//insert installed device
					nextId = sHelper.getNewInstalledId();
	    			dbConn.execSQL("INSERT INTO tbl_Installed" +
	    					" (installedId, installedName, installedX, installedY, installedHeight, installedWidth, tbl_Devices_deviceId, tbl_Walls_wallId) VALUES" +
	    					" ('" + nextId + "', '" + installed.getName() + "', '" + installed.getX() + "', '" + installed.getY() + "', '" + installed.getHeight() + "', '" + installed.getWidth() + "', '" + installed.getDevice().getId() + "', '" + wall.getId() + "')");

	                installed.setId(nextId);
	                
					for(int m=0; m<installed.getConsumationArray().size(); m++){
						Consumation consumation = installed.getConsumationArray().get(m);
						//insert consumation for device
						nextId = sHelper.getNewConsumationId();
		    			dbConn.execSQL("INSERT INTO tbl_Consumations" +
		    					" (consumationId, tbl_Installed_installedId, tbl_Units_unitId, consumationValue) VALUES" +
		    					" ('" + nextId + "', '" + installed.getId() + "', '" + consumation.getUnit().getId() + "', '" + consumation.getValue() + "')");
		    			
		    			consumation.setId(nextId);
					}
				}
			}
		}
		
		//update
		else{
			//update room
			dbConn.execSQL("UPDATE tbl_Rooms" +
					" SET roomName = '" + room.getName() + "'," +
					" tbl_Levels_levelId = '" + levelId + "'" +
					" WHERE roomId = " + room.getId());
			
			for(int k=0; k<room.getWallArray().size(); k++){
	        	wall = room.getWallArray().get(k);
	        	     
		        if(wall.getId() != 0){
		        	//update wall
					dbConn.execSQL("UPDATE tbl_Walls" +
							" SET wallName = '" + wall.getName() + "'," +
							" wallX1 = '" + wall.getX1() + "'," +
							" wallX2 = '" + wall.getX2() + "'," +
							" wallY1 = '" + wall.getY1() + "'," +
							" wallY2 = '" + wall.getY2() + "'," +
							" wallHeight = '" + wall.getHeight() + "'," +
							" wallDepth = '" + wall.getDepth() + "'," +
							" wallType = '" + wall.getWallType() + "'," +
							" tbl_Rooms_roomId = '" + room.getId() + "'" +
							" WHERE wallId = " + wall.getId());
		        }
		        else{
		        	//insert wall
					nextId = sHelper.getNewWallId();
	    			dbConn.execSQL("INSERT INTO tbl_Walls" +
	    					" (wallId, wallName, wallX1, wallX2, wallY1, wallY2, wallHeight, wallDepth, wallType, tbl_Rooms_roomId) VALUES" +
	    					" ('" + nextId + "', '" + wall.getName() + "', '" + wall.getX1() + "', '" + wall.getX2() + "', '" + wall.getY1() + "', '" + wall.getY2() + "', '" + wall.getHeight() + "', '" + wall.getDepth() + "', '" + wall.getWallType() + "', '" + room.getId() + "')");

	                wall.setId(nextId);
		        }
		        
		        for(int l=0; l<wall.getInstalledArray().size(); l++){
		        	installed = wall.getInstalledArray().get(l);
		        	
			        if(installed.getId() != 0){
			        	//update installed device
						dbConn.execSQL("UPDATE tbl_Installed" +
								" SET installedName = '" + installed.getName() + "'," +
								" installedX = '" + installed.getX() + "'," +
								" installedY = '" + installed.getY() + "'," +
								" installedHeight = '" + installed.getHeight() + "'," +
								" installedWidth = '" + installed.getWidth() + "'," +
								" tbl_Devices_deviceId = '" + installed.getDevice().getId() + "'" +
								" WHERE installedId = " + installed.getId());
			        }
			        else{
			        	//insert installed device
						nextId = sHelper.getNewInstalledId();
		    			dbConn.execSQL("INSERT INTO tbl_Installed" +
		    					" (installedId, installedName, installedX, installedY, installedHeight, installedWidth, tbl_Devices_deviceId, tbl_Walls_wallId) VALUES" +
		    					" ('" + nextId + "', '" + installed.getName() + "', '" + installed.getX() + "', '" + installed.getY() + "', '" + installed.getHeight() + "', '" + installed.getWidth() + "', '" + installed.getDevice().getId() + "', '" + wall.getId() + "')");

		                installed.setId(nextId);
			        }
			        
			        for(int m=0; m<installed.getConsumationArray().size(); m++){
			        	consumation = installed.getConsumationArray().get(m);

				        if(consumation.getId() != 0){
				        	//update consumation for device
							dbConn.execSQL("UPDATE tbl_Consumations" +
									" SET tbl_Installed_installedId = '" + installed.getId() + "'," +
									" tbl_Units_unitId = '" + consumation.getUnit().getId() + "'," +
									" consumationValue = '" + consumation.getValue() + "'" +
									" WHERE consumationId = " + consumation.getId());
				        }
				        else{
				        	//insert consumation for device
							nextId = sHelper.getNewConsumationId();
			    			dbConn.execSQL("INSERT INTO tbl_Consumations" +
			    					" (consumationId, tbl_Installed_installedId, tbl_Units_unitId, consumationValue) VALUES" +
			    					" ('" + nextId + "', '" + installed.getId() + "', '" + consumation.getUnit().getId() + "', '" + consumation.getValue() + "')");

			                consumation.setId(nextId);
				        }
			        }
		        }
			}
		}

		a = dbConn.rawQuery("SELECT" +
				" tbl_Buildings_buildingId" +
				" FROM tbl_Levels" +
				" WHERE levelId = " + levelId +
				" LIMIT 1", null);

		context.startManagingCursor(a);
        a.moveToFirst();
        
		setVersion(a.getInt(0));
		
		return room;
	}
	
	@SuppressWarnings("static-access")
	public Installed saveInstalledDevice(Installed installed, int wallId){
		//insert
		if(installed.getId() == 0){
			//insert installed device
			nextId = sHelper.getNewInstalledId();
			dbConn.execSQL("INSERT INTO tbl_Installed" +
					" (installedId, installedName, installedX, installedY, installedHeight, installedWidth, tbl_Devices_deviceId, tbl_Walls_wallId) VALUES" +
					" ('" + nextId + "', '" + installed.getName() + "', '" + installed.getX() + "', '" + installed.getY() + "', '" + installed.getHeight() + "', '" + installed.getWidth() + "', '" + installed.getDevice().getId() + "', '" + wallId + "')");

            installed.setId(nextId);
            
			for(int m=0; m<installed.getConsumationArray().size(); m++){
				Consumation consumation = installed.getConsumationArray().get(m);
				//insert consumation for device
				nextId = sHelper.getNewConsumationId();
    			dbConn.execSQL("INSERT INTO tbl_Consumations" +
    					" (consumationId, tbl_Installed_installedId, tbl_Units_unitId, consumationValue) VALUES" +
    					" ('" + nextId + "', '" + installed.getId() + "', '" + consumation.getUnit().getId() + "', '" + consumation.getValue() + "')");
    			
    			consumation.setId(nextId);
			}
		}
		//update
		else{
			//update installed device
			dbConn.execSQL("UPDATE tbl_Installed" +
					" SET installedName = '" + installed.getName() + "'," +
					" installedX = '" + installed.getX() + "'," +
					" installedY = '" + installed.getY() + "'," +
					" installedHeight = '" + installed.getHeight() + "'," +
					" installedWidth = '" + installed.getWidth() + "'," +
					" tbl_Devices_deviceId = '" + installed.getDevice().getId() + "'" +
					" WHERE installedId = " + installed.getId());
			
			for(int m=0; m<installed.getConsumationArray().size(); m++){
	        	consumation = installed.getConsumationArray().get(m);

		        if(consumation.getId() != 0){
		        	//update consumation for device
					dbConn.execSQL("UPDATE tbl_Consumations" +
							" SET tbl_Installed_installedId = '" + installed.getId() + "'," +
							" tbl_Units_unitId = '" + consumation.getUnit().getId() + "'," +
							" consumationValue = '" + consumation.getValue() + "'" +
							" WHERE consumationId = " + consumation.getId());
		        }
		        else{
		        	//insert consumation for device
					nextId = sHelper.getNewConsumationId();
	    			dbConn.execSQL("INSERT INTO tbl_Consumations" +
	    					" (consumationId, tbl_Installed_installedId, tbl_Units_unitId, consumationValue) VALUES" +
	    					" ('" + nextId + "', '" + installed.getId() + "', '" + consumation.getUnit().getId() + "', '" + consumation.getValue() + "')");

	                consumation.setId(nextId);					        	
		        }
	        }
		}
		
		a = dbConn.rawQuery("SELECT" +
				" tbl_Rooms_roomId" +
				" FROM tbl_Walls" +
				" WHERE wallId = " + wallId +
				" LIMIT 1", null);

		context.startManagingCursor(a);
        a.moveToFirst();
		
		b = dbConn.rawQuery("SELECT" +
				" tbl_Levels_levelId" +
				" FROM tbl_Rooms" +
				" WHERE roomId = " + a.getInt(0) +
				" LIMIT 1", null);

		context.startManagingCursor(b);
        b.moveToFirst();
		
		c = dbConn.rawQuery("SELECT" +
				" tbl_Buildings_buildingId" +
				" FROM tbl_Levels" +
				" WHERE levelId = " + b.getInt(0) +
				" LIMIT 1", null);

		context.startManagingCursor(c);
        c.moveToFirst();
        
		setVersion(c.getInt(0));
		
		return installed;
	}
	
	public void deleteBuildingByBuildingIdAndroid(int buildingId){
		dbConn.execSQL("DELETE FROM tbl_Buildings" +
				" WHERE buildingId = " + buildingId);
	}
	
	public void deleteLevelByLevelIdAndroid(int levelId){
		dbConn.execSQL("DELETE FROM tbl_Levels" +
				" WHERE levelId = " + levelId);
	}
	
	public void deleteRoomByRoomIdAndroid(int roomId){
		dbConn.execSQL("DELETE FROM tbl_Rooms" +
				" WHERE roomId = " + roomId);
	}
	
	public void deleteWallByWallIdAndroid(int wallId){
		dbConn.execSQL("DELETE FROM tbl_Walls" +
				" WHERE wallId = " + wallId);
	}
	
	public void deleteInstalledDeviceByInstalledIdAndroid(int installedId){
		dbConn.execSQL("DELETE FROM tbl_Installed" +
				" WHERE installedId = " + installedId);
	}
	
	public void deleteConsumationByConsumationIdAndroid(int consumationId){
		dbConn.execSQL("DELETE FROM tbl_Consumations" +
				" WHERE consumationId = " + consumationId);
	}
	
	public void deleteBuildingByBuildingId(int buildingId){
		dbConn.execSQL("DELETE FROM tbl_Buildings" +
				" WHERE buildingId = " + buildingId);
		
		SOAPHelper.removeBuilding(buildingId);
	}
	
	public void deleteLevelByLevelId(int levelId){
		a = dbConn.rawQuery("SELECT" +
				" tbl_Buildings_buildingId" +
				" FROM tbl_Levels" +
				" WHERE levelId = " + levelId +
				" LIMIT 1", null);

		context.startManagingCursor(a);
        a.moveToFirst();
		
		setVersion(a.getInt(0));
		
		dbConn.execSQL("DELETE FROM tbl_Levels" +
				" WHERE levelId = " + levelId);
		
		SOAPHelper.removeLevel(levelId);
	}
	
	public void deleteRoomByRoomId(int roomId){
		a = dbConn.rawQuery("SELECT" +
				" tbl_Levels_levelId" +
				" FROM tbl_Rooms" +
				" WHERE roomId = " + roomId +
				" LIMIT 1", null);

		context.startManagingCursor(a);
        a.moveToFirst();
		
		b = dbConn.rawQuery("SELECT" +
				" tbl_Buildings_buildingId" +
				" FROM tbl_Levels" +
				" WHERE levelId = " + a.getInt(0) +
				" LIMIT 1", null);

		setVersion(b.getInt(0));
		
		dbConn.execSQL("DELETE FROM tbl_Rooms" +
				" WHERE roomId = " + roomId);
		
		SOAPHelper.removeRoom(roomId);
	}
	
	public void deleteInstalledDeviceByInstalledId(int installedId){
		a = dbConn.rawQuery("SELECT" +
				" tbl_Walls_wallId" +
				" FROM tbl_Installed" +
				" WHERE installedId = " + installedId +
				" LIMIT 1", null);

		context.startManagingCursor(a);
        a.moveToFirst();
		
		b = dbConn.rawQuery("SELECT" +
				" tbl_Rooms_roomId" +
				" FROM tbl_Walls" +
				" WHERE wallId = " + a.getInt(0) +
				" LIMIT 1", null);

		context.startManagingCursor(b);
        b.moveToFirst();
		
		c = dbConn.rawQuery("SELECT" +
				" tbl_Levels_levelId" +
				" FROM tbl_Rooms" +
				" WHERE roomId = " + b.getInt(0) +
				" LIMIT 1", null);

		context.startManagingCursor(c);
        c.moveToFirst();
		
		d = dbConn.rawQuery("SELECT" +
				" tbl_Buildings_buildingId" +
				" FROM tbl_Levels" +
				" WHERE levelId = " + c.getInt(0) +
				" LIMIT 1", null);

		context.startManagingCursor(d);
        d.moveToFirst();
        
		setVersion(d.getInt(0));
		
		dbConn.execSQL("DELETE FROM tbl_Installed" +
				" WHERE installedId = " + installedId);
		
		SOAPHelper.removeInstalled(installedId);
	}
	
	//get last entry from a table
	public int getLastIdFromTable(String tbl, String idName){
		a = dbConn.rawQuery("SELECT " + idName + " FROM " + tbl + " ORDER BY " + idName + " DESC LIMIT 1", null);
		
		context.startManagingCursor(a);
        a.moveToFirst();
        
        return a.getInt(0);
	}

	public Cursor getBuildings(){
		a = dbConn.rawQuery("SELECT" +
				" buildingId" +
				" FROM tbl_Buildings", null);
		
		return a;
	}
	
	public Cursor getBuildingByBuildingIdCursor(int buildingId){
		a = dbConn.rawQuery("SELECT" +
				" buildingId," +
				" buildingName," +
				" buildingVersion," +
				" tbl_User_userId" +
				" FROM tbl_Buildings" +
				" WHERE buildingId = " + buildingId, null);
		
		return a;
	}
	
	public Cursor getLevelsByBuildingIdCursor(int buildingId){
		a = dbConn.rawQuery("SELECT" +
				" levelId," +
				" levelName," +
				" levelLevel," +
				" tbl_Buildings_buildingId" +
				" FROM tbl_Levels" +
				" WHERE tbl_Buildings_buildingId = " + buildingId, null);
		
		return a;
	}
	
	public Cursor getRoomsByLevelIdCursor(int levelId){
		a = dbConn.rawQuery("SELECT" +
				" roomId," +
				" roomName," +
				" tbl_Levels_levelId" +
				" FROM tbl_Rooms" +
				" WHERE tbl_Levels_levelId = " + levelId, null);
		
		return a;
	}
	
	public Cursor getWallsByRoomIdCursor(int roomId){
		a = dbConn.rawQuery("SELECT" +
				" wallId," +
				" wallName," +
				" wallX1," +
				" wallX2," +
				" wallY1," +
				" wallY2," +
				" wallHeight," +
				" wallDepth," +
				" wallType," +
				" tbl_Rooms_roomId" +
				" FROM tbl_Walls" +
				" WHERE tbl_Rooms_roomId = " + roomId, null);
		
		return a;
	}
	
	public Cursor getInstalledByWallIdCursor(int wallId){
		a = dbConn.rawQuery("SELECT" +
				" installedId," +
				" installedName," +
				" installedX," +
				" installedY," +
				" installedHeight," +
				" installedWidth," +
				" tbl_Devices_deviceId," +
				" tbl_Walls_wallId" +
				" FROM tbl_Installed" +
				" WHERE tbl_Walls_wallId = " + wallId, null);
		
		return a;
	}
	
	public Cursor getConsumationsByInstalledIdCursor(int installedId){
		a = dbConn.rawQuery("SELECT" +
				" consumationId," +
				" consumationValue," +
				" tbl_Installed_installedId," +
				" tbl_Units_unitId" +
				" FROM tbl_Consumations" +
				" WHERE tbl_Installed_installedId = " + installedId, null);
		
		return a;
	}

	public void setVersion(int buildingId){
		int version;
		
		a = getBuildingByBuildingIdCursor(buildingId);

		context.startManagingCursor(a);
        a.moveToFirst();
        
        version = a.getInt(2);
        version++;

		dbConn.execSQL("UPDATE tbl_Buildings" +
				" SET buildingVersion = '" + version + "'" +
				" WHERE buildingId = " + buildingId);
	}
}